package cn.com.ry.spring.transaction.service;

import com.alibaba.druid.pool.DruidDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.Map;
import java.util.UUID;

@Service
public class TransactionService {
    private static final Logger logger = LoggerFactory.getLogger(TransactionService.class);
    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    TransactionService transactionService;

    @Transactional(readOnly = true)
    public void findTransactional() {
        logger.info("【{}】开启read-only事务，当前活跃连接数={}", "findTransactional", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        logger.info("【{}】sleep{}秒", "test_find", 20);
        try {
            Thread.sleep(20000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
//        update("111");
//        jdbcTemplate.queryForList("select * from parenttable");

    }


    public void find() {
        logger.info("【{}】开启read-only事务，当前活跃连接数={}", "test_find", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        logger.info("【{}】sleep{}秒", "test_find", 20);
        try {
            Thread.sleep(20000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        jdbcTemplate.queryForList("select * from parenttable");

    }


    /**
     * 事务回滚
     *
     * @param id 插入的数据
     */
    @Transactional
    public void updateTransactionalError(String id) {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateTransactional", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        jdbcTemplate.update(parentTableSql);
        Integer.parseInt("aaa");
        jdbcTemplate.update(subTableSql);
    }

    /**
     * 异常捕获
     *
     * @param id
     * @throws Exception
     */
    @Transactional(rollbackFor = Exception.class)
    public void updateTransactional(String id) throws Exception {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateTransactional", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        jdbcTemplate.update(parentTableSql);
        if (true) {
            throw new Exception();
        }
        jdbcTemplate.update(subTableSql);


    }

    /**
     * 捕获异常，添加rollback条件
     *
     * @param id
     */
    @Transactional(rollbackFor = Exception.class)
    public void updateTransactionalRollback(String id) {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateTransactional", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        try {
            jdbcTemplate.update(parentTableSql);
            if (true) {
                throw new Exception();
            }
            jdbcTemplate.update(subTableSql);
        } catch (Exception e) {

        }

    }


    /**
     * 抛出异常 添加回滚条件  不回滚
     *
     * @param id 插入的数据
     */
    @Transactional
    public void updateTransactionalException(String id) throws Exception {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateTransactional", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        jdbcTemplate.update(parentTableSql);
        if (true) {
            throw new Exception();
        }
        jdbcTemplate.update(subTableSql);
    }

    /**
     * 抛出异常 添加回滚条件   回滚
     *
     * @param id 插入的数据
     */
    @Transactional(rollbackFor = Exception.class)
    public void updateTransactionalExceptionRollback(String id) throws Exception {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateTransactional", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        jdbcTemplate.update(parentTableSql);
        if (true) {
            throw new Exception();
        }
        jdbcTemplate.update(subTableSql);
    }


    /**
     * @param id 插入的数据
     */
    public void update(String id) {
        logger.info("【{}】开启事务，当前活跃连接数={}", "update", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        jdbcTemplate.update(parentTableSql);
//        if (true) {
//            throw new Exception();
//        }
        jdbcTemplate.update(subTableSql);
    }


    //事务的传播特性
//    @Transactional
    public void updateTransactionNestd(int propagation) {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateTransactionNestd", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        if (propagation == 0) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "REQUIRED");
            transactionService.nestTransactionRequired();
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "REQUIRED");
        } else if (propagation == 1) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "SUPPORTS");
            transactionService.nestTransactionSupports();
        } else if (propagation == 2) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "MANDATORY");
            //没有交给spring管理，注解无效
//            nestTransactionMandatory();
            //通过spring管理执行，注解有效
            transactionService.nestTransactionMandatory();
            logger.info("【{}】【结束】执行嵌套事务，参数【{}】", "updateTransactionNestd", "MANDATORY");
        } else if (propagation == 3) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "REQUIRES_NEW");
            transactionService.nestTransactionRequiresNew();
            logger.info("【{}】【结束】执行嵌套事务，参数【{}】", "updateTransactionNestd", "REQUIRES_NEW");
        } else if (propagation == 4) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "NOT_SUPPORTED");
            transactionService.nestTransactionSupportedNot();
            logger.info("【{}】【结束】执行嵌套事务，参数【{}】", "updateTransactionNestd", "NOT_SUPPORTED");
        } else if (propagation == 5) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "NEVER");
            transactionService.nestTransactionNever();
            logger.info("【{}】【结束】执行嵌套事务，参数【{}】", "updateTransactionNestd", "NEVER");
        } else if (propagation == 6) {
            logger.info("【{}】【开始】执行嵌套事务，参数【{}】", "updateTransactionNestd", "NESTED");
            transactionService.nestTransactionNested();
            logger.info("【{}】【结束】执行嵌套事务，参数【{}】", "updateTransactionNestd", "NESTED");
        }
        logger.info("【{}】【结束】执行嵌套事务", "updateTransactionNestd");

        //模拟异常回滚
//        updateNestdCallback();

    }


    // 保证同一个事务  支持当前事务，如果不存在 就新建一个(默认)
    @Transactional(propagation = Propagation.REQUIRED)
    public void nestTransactionRequired() {
        updateNestd();
    }

    // 保证同一个事务  支持当前事务，如果不存在，就不使用事务
    @Transactional(propagation = Propagation.SUPPORTS)
    public void nestTransactionSupports() {
        updateNestd();
    }

    //  保证同一个事务  支持当前事务，如果不存在，抛出异常
    @Transactional(propagation = Propagation.MANDATORY)
    public void nestTransactionMandatory() {
        updateNestd();
    }

    //    保证没有在同一个事务中 如果有事务存在，挂起当前事务，创建一个新的事务
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public void nestTransactionRequiresNew() {
        updateNestd();
    }

    //保证没有在同一个事务中  以非事务方式运行，如果有事务存在，挂起当前事务
    @Transactional(propagation = Propagation.NOT_SUPPORTED)
    public void nestTransactionSupportedNot() {
        updateNestd();
    }

    //保证没有在同一个事务中 以非事务方式运行，如果有事务存在，抛出异常
    @Transactional(propagation = Propagation.NEVER)
    public void nestTransactionNever() {
        updateNestd();
    }

    //    保证没有在同一个事务中 如果当前事务存在，则嵌套事务执行
    @Transactional(propagation = Propagation.NESTED)
    public void nestTransactionNested() {
        updateNestd();
    }

    //事务的传播特性
    public void updateNestd() {
        logger.info("【{}】开启事务，当前活跃连接数={}", "update", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String id = "nest" + UUID.randomUUID().toString();
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        logger.info("【{}】执行sql={}", "updateNestd", parentTableSql);
        jdbcTemplate.update(parentTableSql);
//        Integer.valueOf("sdfsdf");
        logger.info("【{}】执行sql={}", "updateNestd", subTableSql);
        jdbcTemplate.update(subTableSql);


    }

    //嵌套事务 异常回滚
    public void updateNestdCallback() {
        logger.info("【{}】开启事务，当前活跃连接数={}", "update", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String id = "nest" + UUID.randomUUID().toString();
        String parentTableSql = "insert into parenttable(parent) values('" + id + "')" ;
        String subTableSql = "insert into subtable(sub) values('" + id + "')" ;
        logger.info("【{}】执行sql={}", "updateNestd", parentTableSql);
        jdbcTemplate.update(parentTableSql);
        //模拟异常
        Integer.valueOf("sdfsdf");
        logger.info("【{}】执行sql={}", "updateNestd", subTableSql);
        jdbcTemplate.update(subTableSql);
    }


    //事务隔离级别
    @Transactional
    public void updateIsolation() {
        logger.info("【{}】开启事务，当前活跃连接数={}", "updateIsolation", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String sql = "update numtable set num=num+1 where id=1;" ;
        logger.info("【{}】执行sql={}", "updateIsolation", sql);
        jdbcTemplate.update(sql);
        try {
            Thread.sleep(5000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    @Transactional(isolation = Isolation.READ_UNCOMMITTED)
    public void getIsolation() {
        try {
            Thread.sleep(1000);
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        logger.info("【{}】开启事务，当前活跃连接数={}", "getIsolation", ((DruidDataSource) jdbcTemplate.getDataSource()).getActiveCount());
        String sql = "select id,num from numtable where id=1" ;
        Map queryMap = jdbcTemplate.queryForMap(sql);
        //查询到的num数据
        logger.info("【{}】查询到的值num={},执行sql={}", "getIsolation", queryMap.get("num"), sql);
//        try {
//            Thread.sleep(200000);
//        } catch (InterruptedException e) {
//            e.printStackTrace();
//        }
    }
}


